﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable suppl;
        private delegate void Deleg();
        private Deleg del;    
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            LoadSuppl();
            LoadGroupSuppl();
        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void HideShowRows()
        {
            if (dataGridView1.DataSource != null)
            {
                CurrencyManager cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                cm.SuspendBinding();

                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    r.Visible = true;

                    if (!Regex.Match(Convert.ToString(dataGridView1[dataGridView1.SortedColumn.Name, r.Index].Value), textBox1.Text, RegexOptions.IgnoreCase).Success)
                        r.Visible = false;

                    if (checkBox1.Checked)
                        if (!Convert.ToBoolean(dataGridView1[namechcol, r.Index].Value)) r.Visible = false;
                }

                cm.ResumeBinding();
            }
        }

        private void LoadSuppl()
        {
            dataGridView1.DataSource = null;
            suppl = new DataTable();
            suppl.Load(ProcSel(conn, @"pas_SupplSel"));
            foreach (DataColumn dc in suppl.Columns)
            {
                if (dc.ColumnName != "Флаг") dc.ReadOnly = true;
                else dc.ReadOnly = false;
            }
            dataGridView1.DataSource = suppl;
            dataGridView1.Sort(dataGridView1.Columns[2], ListSortDirection.Ascending);
            dataGridView2.Columns.Clear();
            foreach (DataGridViewColumn col in dataGridView1.Columns)
            {
                if(col.Index == 0)
                {
                    dataGridView2.Columns.Add(new DataGridViewCheckBoxColumn());
                    dataGridView2.Columns[0].HeaderText = col.HeaderText;
                    dataGridView2.Columns[0].Name = col.Name;
                }
                else
                    dataGridView2.Columns.Add(col.Name,col.HeaderText);
            }
        }

        private SqlDataReader ProcSel(SqlConnection con, string procname)
        {
            SqlCommand com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = procname;
            if (con.State == ConnectionState.Closed) con.Open();
            return com.ExecuteReader();
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource != null)
            {
                CurrencyManager cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                cm.SuspendBinding();
                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    if (r.Visible)
                        dataGridView1[namechcol, r.Index].Value = true;
                }
                cm.ResumeBinding();
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource != null)
            {
                CurrencyManager cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                cm.SuspendBinding();
                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    if (r.Visible)
                        dataGridView1[namechcol, r.Index].Value = false;
                }
                cm.ResumeBinding();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox2.Text != "")
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[1];
                par[0].name = "@grsup";
                par[0].value = textBox2.Text;
                (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_GroupSupplierIns", 30, par);
                LoadGroupSuppl();
            }
            else
            {
                MessageBox.Show("Не введен новый поставщик");
                textBox2.Focus();
            }

        }

        private void LoadGroupSuppl()
        {
            DataTable dt = new DataTable();
            dt = ((new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_GroupSupplierSel", 30, null));
            comboBox1.Text = "";
            comboBox1.Items.Clear();
            foreach (DataRow row in dt.Rows)
            {
                NewItem ni = new NewItem();
                ni.ParamID = row[0];
                ni.ParamComment = row[1].ToString();

                comboBox1.Items.Add(ni);
            }
            comboBox1.SelectedIndex = -1;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex > -1)
            {
                int grsup = Convert.ToInt32((comboBox1.SelectedItem as NewItem).ParamID);
                if (checkBox2.Checked)
                {
                    if (MessageBox.Show("Изменение коснется ВСЕХ выбранных строк!\nВы уверены?", "Внимание!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
                    {
                        int upd = 0;
                        foreach (DataGridViewRow row in dataGridView1.Rows)
                        {
                            if (Convert.ToBoolean(row.Cells[0].Value))
                            {
                                GroupSupplUpd(Convert.ToInt32(row.Cells[1].Value), grsup);
                                upd++;
                            }
                        }
                        LoadSuppl();
                        MessageBox.Show("Всего строк обработано: " + upd.ToString());
                    }
                }
                else
                {
                    if (dataGridView1.CurrentCell != null)
                    {
                        GroupSupplUpd(Convert.ToInt32(dataGridView1[1,dataGridView1.CurrentCell.RowIndex].Value), grsup);
                        LoadSuppl();
                    }
                    else MessageBox.Show("Не выбрана строка");
                } 
            }
            else
            {
                MessageBox.Show("Не выбрана группа поставщиков");
                comboBox1.Focus();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex > -1)
            {
                if (checkBox2.Checked)
                {
                    if (MessageBox.Show("Изменение коснется ВСЕХ выбранных строк!\nВы уверены?", "Внимание!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
                    {
                        int upd = 0;
                        foreach (DataGridViewRow row in dataGridView1.Rows)
                        {
                            if (Convert.ToBoolean(row.Cells[1].Value))
                            {
                                GroupSupplUpd(Convert.ToInt32(row.Cells[0].Value), null);
                                upd++;
                            }
                        }
                        LoadSuppl();
                        MessageBox.Show("Всего строк обработано: " + upd.ToString());
                    }
                }
                else
                {
                    if (dataGridView1.CurrentCell != null)
                    {
                        GroupSupplUpd(Convert.ToInt32(dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value), null);
                        LoadSuppl();
                    }
                    else MessageBox.Show("Не выбрана строка");
                }
            }
            else
            {
                MessageBox.Show("Не выбрана группа поставщиков");
                comboBox1.Focus();
            }
        }

        private void GroupSupplUpd(int supcode,object grsup)
        {
            SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[2];
            par[0].name = "@supcode";
            par[0].value = supcode;
            par[1].name = "@grsup";
            par[1].value = grsup;
            (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_SupplGroupSupplUpd", 30, par);
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox2.Checked) MessageBox.Show("Изменение коснется ВСЕХ выбранных поставщиков!");
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.CurrentCell.ColumnIndex == 0)
            {
                if (Convert.ToBoolean(dataGridView1.CurrentCell.Value))
                {
                    List<object> r = new List<object>();// = dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells;
                    foreach (DataGridViewCell cell in dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells)
                    {
                        r.Add(cell.Value);
                    }
                    dataGridView2.Rows.Add(r.ToArray());
                }
                else
                {
                    for (int i = 0; i < dataGridView2.RowCount; i++)
                    {
                        if (Convert.ToInt32(dataGridView2[1, i].Value) == Convert.ToInt32(dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value))
                        {
                            dataGridView2.Rows.RemoveAt(i);
                            break;
                        }
                    }
                }
            }
        }

        private void dataGridView2_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if(dataGridView2.RowCount > 0)
                if (dataGridView2.CurrentCell.ColumnIndex == 0)
                {
                    if (!Convert.ToBoolean(dataGridView1.CurrentCell.Value))
                    {

                        for (int i = 0; i < dataGridView1.RowCount; i++)
                        {
                            if (Convert.ToInt32(dataGridView1[1, i].Value) == Convert.ToInt32(dataGridView2[1, dataGridView2.CurrentCell.RowIndex].Value))
                            {
                                dataGridView1[0, i].Value = false;
                                dataGridView2.Rows.RemoveAt(dataGridView2.CurrentCell.RowIndex);
                                break;
                            }
                        }
                    }
                }

        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {

        }


    }
}
